﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Text;
using Cosmetic.DataAccess;
using Cosmetic.DataEntity;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Cosmetic.Logic
{
    /// <summary>
    /// Podeuct manager class
    /// </summary>
    public class NewsManger : LogicBase
    {
        private NewClassManger m_CategoryManager = new NewClassManger();
      
        /// <summary>
        /// Insert a new news into database
        /// </summary>
        /// <param name="aProduct">new product</param>
        public void Insert(news aProduct)
        {
            m_CosmeticDC.news.InsertOnSubmit(aProduct);
            m_CosmeticDC.SubmitChanges();
        }

        /// <summary>
        /// Delete product
        /// </summary>
        /// <param name="aProduct">The product want delete</param>
        public void Delete(news  aProduct)
        {
           
            m_CosmeticDC.news .DeleteOnSubmit(aProduct);
            m_CosmeticDC.SubmitChanges();
        }

       


     

      

        /// <summary>
        /// Get product by product Id
        /// </summary>
        /// <param name="aProductId">Product Id</param>
        /// <returns>Product</returns>
        public news  GetProductById(Guid aProductId)
        {
            return m_CosmeticDC.news .Single(theProect => theProect.newid == aProductId);
        }


       


        public List<ProductInfo> GetProductInfoByProductCode(string aProductCode)
        {
            var result = (from theProduct in m_CosmeticDC.ProductInfos
                          where theProduct.ProductCode == aProductCode
                          orderby theProduct.Name descending
                          select theProduct).ToList();

            return result;
        }

        /// <summary>
        /// get product by brand id
        /// </summary>
        /// <param name="aBrandId">brand id</param>
        /// <returns>product list</returns>
        public List<Product> GetProductByBrand(Guid aBrandId)
        {
            return (from theProduct in m_CosmeticDC.Products
                    where theProduct.BrandId == aBrandId
                    orderby theProduct.Name descending
                    select theProduct).ToList();
            //return aBrand.Products.OrderBy(theProduct => theProduct.Name).ToList();
        }
        /// <summary>
        /// get product by brand
        /// </summary>
        /// <param name="aBrand">brand</param>
        /// <returns>product list</returns>
        public List<Product> GetProductByBrand(Brand aBrand)
        {
            return this.GetProductByBrand(aBrand.BrandId);
            //return aBrand.Products.OrderBy(theProduct => theProduct.Name).ToList();
        }
        /// <summary>
        /// get product by series
        /// </summary>
        /// <param name="aSeries">series</param>
        /// <returns>product list</returns>
        public List<Product> GetProductBySeries(ProductSeries aSeries)
        {
            return (from theProduct in m_CosmeticDC.Products
                    where theProduct.ProductSeriesId == aSeries.ProductSeriesId
                    orderby theProduct.Name descending
                    select theProduct).ToList();
            //return aSeries.Products.OrderBy(theProduct => theProduct.Name).ToList();
        }
        /// <summary>
        /// get product by brand order by comment count
        /// </summary>
        /// <param name="aBrandId">the brand id</param>
        /// <param name="aStartDate"> comment create date after the startdate</param>
        /// <param name="anEndDate">comment create date before the end date</param>
        /// <param name="aCount">how mang product wanna get</param>
        /// <returns>product list</returns>
        public List<Product> GetProductByBrandOrderComments(Guid aBrandId, DateTime aStartDate, DateTime anEndDate, int aCount)
        {
            return (from theProduct in m_CosmeticDC.Products
                    join theComment in m_CosmeticDC.Comments on theProduct.ProductId equals theComment.ProductId
                    where theProduct.BrandId == aBrandId
                    && aStartDate.Date <= theComment.CreateTime.Date
                    && theComment.CreateTime.Date <= anEndDate.Date
                    orderby theProduct.Comments.Count descending
                    select theProduct).Distinct().Take(aCount).ToList();
        }
        /// <summary>
        /// get product by category order by comment count
        /// </summary>
        /// <param name="aCategoryId">the category id</param>
        /// <param name="aStartDate"> comment create date after the startdate</param>
        /// <param name="anEndDate">comment create date before the end date</param>
        /// <param name="aCount">how mang product wanna get</param>
        /// <returns>product list</returns>
        public List<Product> GetProductByCategoryOrderComments(Guid aCategoryId, DateTime aStartDate, DateTime anEndDate, int aCount)
        {
            return (from theProduct in m_CosmeticDC.Products
                    join theComment in m_CosmeticDC.Comments on theProduct.ProductId equals theComment.ProductId
                    where theProduct.ProductCategoryId == aCategoryId
                    && aStartDate.Date <= theComment.CreateTime.Date
                    && theComment.CreateTime.Date <= anEndDate.Date
                    orderby theProduct.Comments.Count descending
                    select theProduct).Distinct().Take(aCount).ToList();
        }
        /// <summary>
        /// fuzzy search product by the part or full product name
        /// </summary>
        /// <param name="aProductName">the part or full product name</param>
        /// <returns>product list</returns>
        public List<Product> GetProductByName(string aProductName)
        {
            return this.GetProductByName(aProductName, m_CosmeticDC.Products.ToList());
        }
        /// <summary>
        /// fuzzy search product by the part or full product name
        /// </summary>
        /// <param name="aProductName">the part or full product name</param>
        /// <param name="aProductList">search range</param>
        /// <returns>product list</returns>
        private List<Product> GetProductByName(string aProductName, List<Product> aProductList)
        {
            return (from theProduct in aProductList
                    where theProduct.Name.ToLower().Contains(aProductName.ToLower())
                    orderby theProduct.Name
                    select theProduct).ToList();
        }

        public List<ProductInfo> GetProductInfoByName(string aProductName)
        {
            var result = (from theProductInfo in m_CosmeticDC.ProductInfos
                          where theProductInfo.Name.ToLower().Contains(aProductName.ToLower())
                          orderby theProductInfo.Name
                          select theProductInfo).ToList();
            return result;
        }


        /// <summary>
        /// specified the brand fuzzy search product by the part or full product name
        /// </summary>
        /// <param name="aProductName">the part or full product name</param>
        /// <param name="aBrand">the specified brand</param>
        /// <returns>product list</returns>
        public List<Product> GetProductByName(string aProductName, Brand aBrand)
        {
            return this.GetProductByName(aProductName, this.GetProductByBrand(aBrand));
        }
        /// <summary>
        /// specified the series fuzzy search product by the part or full product name 
        /// </summary>
        /// <param name="aProductName">the part or full product name</param>
        /// <param name="aSeries">the specified series</param>
        /// <returns>product list</returns>
        public List<Product> GetProductByName(string aProductName, ProductSeries aSeries)
        {
            return this.GetProductByName(aProductName, this.GetProductBySeries(aSeries));
        }
        /// <summary>
        /// specified the category fuzzy search product by the part or full product name 
        /// </summary>
        /// <param name="aProductName">the part or full product name</param>
        /// <param name="aCategory">the specified series</param>
        /// <returns>product list</returns>
        //public List<Product> GetProductByName(string aProductName, Category aCategory)
        //{
        //    return this.GetProductByName(aProductName, this.GetProductByCategory(aCategory));
        //}
        ///// <summary>
        ///// get all products
        ///// </summary>
        ///// <returns>all products</returns>
        //public List<Product> GetAll()
        //{
        //    return m_CosmeticDC.Products.ToList();
        //}

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public List<news> GetAll()
        {
            return (from theActivity in m_CosmeticDC.news 
                    join thenewclass in m_CosmeticDC.newclass
                    on theActivity.newCategoryId equals thenewclass.CategoryId
                    orderby theActivity.createTime  descending
                    select theActivity).ToList();
        }
      
        /// <summary>
        /// get product by product category id
        /// </summary>
        /// <param name="aCategoryId">product category id</param>
        /// <returns>product list</returns>
        public List<Product> GetProductByCategory(Guid aCategoryId)
        {
            var result = (from theProduct in m_CosmeticDC.Products
                          where theProduct.ProductCategoryId == aCategoryId
                          orderby theProduct.Name
                          select theProduct).ToList();
            return result;
        }
        /// <summary>
        /// get product by product category 
        /// </summary>
        /// <param name="aCategory">product category</param>
        /// <returns>product list</returns>
        public List<Product> GetProductByCategory(Category aCategory)
        {
            return this.GetProductByCategory(aCategory.CategoryId);
        }
        /// <summary>
        /// get product by product category id
        /// </summary>
        /// <param name="aCategoryId">product category id</param>
        /// <param name="aName">part or full product name</param>
        /// <returns>product list</returns>
        public List<Product> GetProductByCategory(Guid aCategoryId, string aName)
        {
            return (from theProduct in m_CosmeticDC.Products
                    where theProduct.ProductCategoryId == aCategoryId
                    && theProduct.Name.Contains(aName)
                    orderby theProduct.Name
                    select theProduct).ToList();
        }
        /// <summary>
        /// get product by product category 
        /// </summary>
        /// <param name="aCategory">product category</param>
        /// <param name="aName">part or full product name</param>
        /// <returns>product list</returns>
        public List<Product> GetProductByCategory(Category aCategory, string aName)
        {
            return this.GetProductByCategory(aCategory.CategoryId, aName);
        }


        public static DataTable GetProductInfoByIds(Guid aProductId)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            
               try
            {
                con.Open();
                string cmdStr = string.Empty;
                cmdStr = "select contents,title,newCategoryId,createTime from news where newid='" + aProductId  + "'";
                SqlCommand com = new SqlCommand(cmdStr, con);
                SqlDataAdapter adap = new SqlDataAdapter(com);
                DataSet ds = new DataSet();
                adap.Fill(ds);
                return ds.Tables[0];
            }
            finally
            {
                con.Close();
            }
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="aProductId"></param>
        /// <returns></returns>
        public news GetProductInfoById(Guid aProductId)
        {
            return m_CosmeticDC.news .Single(theProectInfo => theProectInfo.newid == aProductId);
        }

        /// <summary>
        /// get all products
        /// </summary>
        /// <returns>all products</returns>
        public List<ProductInfo> GetAll_L()
        {
            return m_CosmeticDC.ProductInfos.ToList();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public List<ProductInfo> GetNewArrivalProduct()
        {
            var result = (from theProductInfo in m_CosmeticDC.ProductInfos
                          join theNewProduct in m_CosmeticDC.NewArrivalProducts
                          on theProductInfo.ProductId equals theNewProduct.ProductId
                          orderby theNewProduct.Sequence ascending
                          select theProductInfo).ToList();
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="aProduct"></param>
        /// <returns></returns>
        public List<ProductInfo> GetSimilarProductInfo(Product aProduct)
        {
            if (aProduct.Name.Contains("（"))
            {
                return (from theProductInfo in m_CosmeticDC.ProductInfos
                        where theProductInfo.Name.StartsWith(aProduct.Name.Substring(0, aProduct.Name.IndexOf('（'))) && theProductInfo.ProductId != aProduct.ProductId
                        orderby theProductInfo.ProductCode
                        select theProductInfo).ToList();
            }
            else
            {
                return null;
            }
            //return aBrand.Products.OrderBy(theProduct => theProduct.Name).ToList();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="aProduct"></param>
        /// <returns></returns>
        public List<ProductSize> GetProductSize(Product aProduct)
        {
            return (from theProductSize in m_CosmeticDC.ProductSizes
                    where theProductSize.GoodsCode == aProduct.ProductCode
                    orderby theProductSize.Size
                    select theProductSize).ToList();
        }

        public List<ProductSize> GetProductSize(Guid ProductId)
        {
            string ProductCode = (from theProduct in m_CosmeticDC.Products
                                  where theProduct.ProductId == ProductId
                                  select theProduct.ProductCode).Single();

            return (from theProductSize in m_CosmeticDC.ProductSizes
                    where theProductSize.GoodsCode == ProductCode
                    orderby theProductSize.Size
                    select theProductSize).ToList();

        }


        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        /// 
        public List<ProductInfo> GetRecommandProductInfo()
        {
            return (from theProductInfo in m_CosmeticDC.ProductInfos
                    where theProductInfo.Recommend.GetValueOrDefault()
                    orderby theProductInfo.RecommendTime descending
                    select theProductInfo).ToList();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        /// 
        public List<ProductInfo> GetFocusProductInfo(int count)
        {
            return (from theProductInfo in m_CosmeticDC.ProductInfos
                    where theProductInfo.Focus.GetValueOrDefault()
                    orderby theProductInfo.RecommendTime descending
                    select theProductInfo).Take(count).ToList();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public IEnumerable GetCurrentMonthSalesProductInfo(int count)
        {
            var result = from theMonthSale in m_CosmeticDC.MonthlySales
                         join theproduct in m_CosmeticDC.ProductInfos
                         on theMonthSale.ProductId equals theproduct.ProductId
                         orderby theMonthSale.Sequence
                         select new
                         {
                             ProductId = theproduct.ProductId,
                             ProductName = theproduct.Name,
                             PictureUrl = theproduct.PictureUrl,
                             ProductPrice = theproduct.网络零售价
                         };
            return result.Take(count);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public MonthlySaleImage GetMonthlySaleImage()
        {
            return m_CosmeticDC.MonthlySaleImages.First();
        }

        public void UpdateMonthlySaleImage(string PicUrl, string ProductId)
        {
            MonthlySaleImage dd = m_CosmeticDC.MonthlySaleImages.First();
            dd.PicUrl = PicUrl;
            dd.ProductId = new Guid(ProductId);

            m_CosmeticDC.SubmitChanges();
        }



        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public WeeklySaleImage GetWeeklySaleImage()
        {
            return m_CosmeticDC.WeeklySaleImages.First();
        }

        public void UpdateWeeklySaleImage(string PicUrl, string ProductId)
        {
            WeeklySaleImage dd = m_CosmeticDC.WeeklySaleImages.First();
            dd.PicUrl = PicUrl;
            dd.ProductId = new Guid(ProductId);

            m_CosmeticDC.SubmitChanges();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public IEnumerable GetCurrentWeeklySalesProductInfo(int count)
        {
            var result = from theWeeklySale in m_CosmeticDC.WeeklySales
                         join theproduct in m_CosmeticDC.ProductInfos
                         on theWeeklySale.ProductId equals theproduct.ProductId
                         orderby theWeeklySale.Sequence
                         select new
                         {
                             ProductId = theproduct.ProductId,
                             ProductName = theproduct.Name,
                             PictureUrl = theproduct.PictureUrl,
                             ProductPrice = theproduct.网络零售价
                         };
            return result.Take(count);
        }


        public List<ProductInfo> GetCurrentMonthFocusProductInfo(int count)
        {

            //select a.ProductId,count(*) number from dbo.OrderDetail a join dbo.[Order] b on a.OrderId = b.OrderId
            //where b.CreateTime < getdate()
            //group by a.ProductId 
            //order by number desc




            //return (from theProductInfo in m_CosmeticDC.ProductInfos
            //        where theProductInfo.Focus.GetValueOrDefault()
            //        && theProductInfo.RecommendTime.Value.Month == DateTime.Today.Month
            //        orderby theProductInfo.RecommendTime descending
            //        select theProductInfo).ToList();

            var result = from theOrderDetail in m_CosmeticDC.OrderDetails
                         join theOrder in m_CosmeticDC.Orders
                             on theOrderDetail.OrderId equals theOrder.OrderId
                         where theOrder.CreateTime.Month == DateTime.Today.Month
                         select theOrderDetail;
            var secondresult = (from theItem in result
                                group theItem by theItem.ProductId into g
                                select new
                                {
                                    g.Key,
                                    TotalPrice = g.Count()
                                });
            var finalresult = from item in secondresult
                              orderby item.TotalPrice descending
                              select new
                              {
                                  key = item.Key.ToString()
                              };

            var ffresult = (from it in finalresult
                            join dd in m_CosmeticDC.ProductInfos
                            on it.key equals dd.ProductId.ToString()
                            select dd).Take(count).ToList();

            return ffresult;


        }

        public List<ProductInfo> GetCurrentWeekFocusProductInfo(int count)
        {
            //return (from theProductInfo in m_CosmeticDC.ProductInfos
            //        where theProductInfo.Focus.GetValueOrDefault()
            //        && theProductInfo.RecommendTime.Value >= DateTime.Today.AddDays(-3) && theProductInfo.RecommendTime.Value <= DateTime.Today.AddDays(3)
            //        orderby theProductInfo.RecommendTime descending
            //        select theProductInfo).ToList();

            var result = from theOrderDetail in m_CosmeticDC.OrderDetails
                         join theOrder in m_CosmeticDC.Orders
                             on theOrderDetail.OrderId equals theOrder.OrderId
                         where theOrder.CreateTime >= DateTime.Today.AddDays(-3) && theOrder.CreateTime <= DateTime.Today.AddDays(3)
                         select theOrderDetail;
            var secondresult = (from theItem in result
                                group theItem by theItem.ProductId into g
                                select new
                                {
                                    g.Key,
                                    TotalPrice = g.Count()
                                });
            var finalresult = from item in secondresult
                              orderby item.TotalPrice descending
                              select new
                              {
                                  key = item.Key.ToString()
                              };

            var ffresult = (from it in finalresult
                            join dd in m_CosmeticDC.ProductInfos
                            on it.key equals dd.ProductId.ToString()
                            select dd).Take(count).ToList();

            return ffresult;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        /// 
        public List<ProductInfo> GetPointProductInfo()
        {
            return (from theProductInfo in m_CosmeticDC.ProductInfos
                    where theProductInfo.Point.GetValueOrDefault()
                    orderby theProductInfo.RecommendTime descending
                    select theProductInfo).ToList();
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        /// 
        public List<ProductInfo> GetFavoriteProductInfo(Guid aUserId)
        {
            return (from theProductInfo in m_CosmeticDC.ProductInfos
                    join theFavorite in m_CosmeticDC.Favorites on theProductInfo.ProductId equals theFavorite.ProductId
                    where theFavorite.UserId == aUserId
                    orderby theFavorite.CreateTime descending
                    select theProductInfo).ToList();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="aProduct"></param>
        /// <returns></returns>
        public List<ProductInfo> GetProductInfoByBrand(Product aProduct)
        {
            return (from theProductInfo in m_CosmeticDC.ProductInfos
                    where theProductInfo.BrandId == aProduct.BrandId && theProductInfo.ProductId != aProduct.ProductId
                    orderby theProductInfo.CreateTime descending
                    select theProductInfo).ToList();
            //return aBrand.Products.OrderBy(theProduct => theProduct.Name).ToList();
        }


        /// <summary>
        /// 本类热销for CategoryToProducts.aspx
        /// </summary>
        /// <param name="aCategoryId"></param>
        /// <returns></returns>
        public List<ProductInfo> GetProductInfoByCategoryId(Guid aCategoryId, int count)
        {
            var result = (from theProduct in m_CosmeticDC.ProductInfos
                          where theProduct.ProductCategoryId == aCategoryId
                          orderby theProduct.Views descending
                          select theProduct).Take(count);
            return result.ToList();
        }

        public List<ProductInfo> GetProductInfoBySupCategoryId(Guid aCategoryId, int count)
        {
            var result = (from theProduct in m_CosmeticDC.ProductInfos
                          join theCategory in m_CosmeticDC.Categories
                          on theProduct.ProductCategoryId equals theCategory.CategoryId
                          where theCategory.SupCategoryId == aCategoryId
                          orderby theProduct.Views descending
                          select theProduct).Take(count);
            return result.ToList();
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="aBrand"></param>
        /// <returns></returns>
        public List<ProductInfo> GetProductInfoByBrand(Brand aBrand)
        {
            return (from theProductInfo in m_CosmeticDC.ProductInfos
                    where theProductInfo.BrandId == aBrand.BrandId
                    orderby theProductInfo.CreateTime descending
                    select theProductInfo).ToList();
            //return aBrand.Products.OrderBy(theProduct => theProduct.Name).ToList();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="aCategoryId"></param>
        /// <returns></returns>
        public List<news > GetProductByCategory_L(Guid aCategoryId)
        {
            var result = (from theProduct in m_CosmeticDC.news 
                          where theProduct.newCategoryId  == aCategoryId
                          orderby theProduct.createTime  descending
                          select theProduct).ToList();
            return result;
        }
        public List<news> GetProductByCategory_Ls(int  aCategoryId)
        {
            var result = (from theProduct in m_CosmeticDC.news
                          where theProduct.classid == aCategoryId
                          orderby theProduct.createTime descending
                          select theProduct).ToList();
            return result;
        }
        public List<news> GetProductByCategory_Les()
        {
            var result = (from theProduct in m_CosmeticDC.news
                          orderby theProduct.createTime descending
                          select theProduct).ToList();
            return result;
        }
     
        public IEnumerable GetProductByCategory_Les1()
        {
            var result = from thePlate in m_CosmeticDC.news
                         orderby thePlate.createTime descending
                         select thePlate;
            return result.Take(6);
        }




        /// <summary>
        /// 
        /// </summary>
        /// <param name="aCategory"></param>
        /// <returns></returns>
        public List<ProductInfo> GetProductInfoByFirstCategory(Category aCategory)
        {
            var result = (from theProduct in m_CosmeticDC.ProductInfos
                          join theCategory in m_CosmeticDC.Categories
                          on theProduct.ProductCategoryId equals theCategory.CategoryId
                          where theCategory.SupCategory == aCategory
                          orderby theProduct.CreateTime descending
                          select theProduct).ToList();
            return result;
        }

        ///
        public List<ProductInfo> GetProductInfoByFirstCategory(Guid aCategoryId)
        {
            var result = (from theProduct in m_CosmeticDC.ProductInfos
                          join theCategory in m_CosmeticDC.Categories
                          on theProduct.ProductCategoryId equals theCategory.CategoryId
                          where theCategory.SupCategoryId == aCategoryId
                          orderby theProduct.CreateTime descending
                          select theProduct).ToList();
            return result;
        }

        /// <summary>
        /// 获得最高级的某个分类的商品
        /// </summary>
        /// <param name="aCategoryId"></param>
        /// <returns></returns>
        public List<ProductInfo> GetProductInfoByTopCategory(Guid aCategoryId)
        {
            var result = (from theProduct in m_CosmeticDC.ProductInfos
                          join theCategory in m_CosmeticDC.Categories
                          on theProduct.ProductCategoryId equals theCategory.CategoryId
                          where theCategory.SupCategory.SupCategoryId == aCategoryId
                          orderby theProduct.CreateTime descending
                          select theProduct).ToList();
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="aCategoryId"></param>
        /// <returns></returns>
        public List<news > GetProductByFirstCategory(Guid aCategoryId)
        {
            var result = (from theProduct in m_CosmeticDC.news 
                          join theCategory in m_CosmeticDC.newclass
                          on theProduct.newCategoryId equals theCategory.CategoryId
                          where theCategory.CategoryId == aCategoryId
                          orderby theProduct.createTime descending
                          select theProduct).ToList();
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="brand"></param>
        /// <param name="price"></param>
        /// <param name="type"></param>
        /// <param name="style"></param>
        /// <returns></returns>
        public DataSet SearchProductInfo(string brand, string price, string type, string style, string order)
        {
            if (brand == "" || price == "" || type == "" || order == "")
            {
                return null;
            }
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            con.Open();
            try
            {
                string sql = "select * from ProductInfo where 1=1 ";
                if (brand != "-1")
                {
                    sql += " and 品牌 like '%" + brand + "%' ";
                }
                if (price != "-1")
                {
                    if (price == "9")
                    {
                        sql += " and 标准价格> 400 ";
                    }
                    else if (price == "1")
                    {
                        sql += " and 标准价格>=1 and 标准价格<= 49 ";
                    }
                    else
                    {
                        int intprice = int.Parse(price);
                        sql += " and 标准价格 >= " + ((intprice - 1) * 50).ToString() + "and 标准价格 <= " + (intprice * 50 - 1).ToString();
                    }
                }
                if (type != "-1")
                {
                    sql += " and 类型 like '%" + type + "%'";
                }
                if (style != "-1")
                {
                    sql += " and 功能 like '%" + style + "%'";
                }
                if (order != "")
                {
                    if (order == "PA")
                    {
                        sql += " order by 网络零售价 asc";
                    }
                    else if (order == "PD")
                    {
                        sql += " order by 网络零售价 desc";
                    }
                    else if (order == "CA")
                    {
                        sql += " order by PublishDate asc";
                    }
                    else if (order == "CD")
                    {
                        sql += " order by PublishDate desc";
                    }
                    else
                    {
                        sql += " order by Views desc";
                    }

                }
                SqlCommand com = new SqlCommand(sql, con);

                DataSet ds = new DataSet();

                SqlDataAdapter da = new SqlDataAdapter(com);
                da.Fill(ds);

                return ds;
            }
            catch (Exception ex)
            {
                //con.Close();
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="aName"></param>
        /// <returns></returns>
        public List<ProductInfo> SearchProductInfoByName(string aName)
        {
            string[] theNames = aName.Split(' ');
            IQueryable<ProductInfo> theProductInfos = m_CosmeticDC.ProductInfos;
            foreach (string theName in theNames)
            {
                theProductInfos = SearchProductInfoByName(theProductInfos, theName);
            }
            return theProductInfos.ToList();
        }

        public IEnumerable GetHotKeyWord(int count)
        {
            var result = from theHotkey in m_CosmeticDC.HotKeyWord
                         orderby theHotkey.ViewTimes descending
                         select theHotkey;
            return result.Take(count);
        }

        public void IncreaseKeyWord(string content)
        {
            var result = from theHotkey in m_CosmeticDC.HotKeyWord 
                         where theHotkey.HotKeyWord1 == content
                         select theHotkey;
            if (result.Count() == 0)
            {
                HotKeyWord newkeyword = new HotKeyWord();
                newkeyword.HotKeyWord1 = content;
                newkeyword.CreateTime = DateTime.Now;
                newkeyword.ViewTimes = 1;

                m_CosmeticDC.HotKeyWord.InsertOnSubmit(newkeyword);
            }
            else
            {
                result.Single().ViewTimes++;
            }

            m_CosmeticDC.SubmitChanges();
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="aProductInfos"></param>
        /// <param name="aName"></param>
        /// <returns></returns>
        public IQueryable<ProductInfo> SearchProductInfoByName(IQueryable<ProductInfo> aProductInfos, string aName)
        {
            return (from theProductInfo in aProductInfos
                    where theProductInfo.Name.Contains(aName) //|| theProductInfo.ProductCode.Contains(aName)
                    select theProductInfo);
        }


        /// <summary>
        /// get specify product skin fit
        /// </summary>
        /// <param name="aProduct">the specify product</param>
        /// <returns>product fit list</returns>
        //public List<news > GetProductSkinFitByProduct(Product aProduct)
        //{
        //    Category theSkinTopCategory = this.m_CategoryManager.GetTopCategryByName(CategoryManager.ETopCategory.UserSkin);
        //    return (from theProductFit in m_CosmeticDC.ProductFits
        //            where theProductFit.ProductId == aProduct.ProductId && theProductFit.FitCategory.SupCategoryId == theSkinTopCategory.CategoryId
        //            select theProductFit).ToList();
        //}
        ///// <summary>
        ///// get specify product hair fit
        ///// </summary>
        ///// <param name="aProduct">the specify product</param>
        ///// <returns>product fit list</returns>
        //public List<ProductFit> GetProductHairFitByProduct(Product aProduct)
        //{
        //    Category theHairTopCategory = this.m_CategoryManager.GetTopCategryByName(CategoryManager.ETopCategory.UserHair);
        //    return (from theProductFit in m_CosmeticDC.ProductFits
        //            where theProductFit.ProductId == aProduct.ProductId && theProductFit.FitCategory.SupCategoryId == theHairTopCategory.CategoryId
        //            select theProductFit).ToList();
        //}


        ///// <summary>
        ///// get the products' instance 
        ///// </summary>
        ///// <param name="ht"></param>
        ///// <returns></returns>
        //public IList GetProducts(Hashtable ht)
        //{

        //    Guid[] pp = new Guid[ht.Count];
        //    int i = 0;
        //    foreach (DictionaryEntry entry in ht)
        //    {
        //        pp[i] = new Guid(entry.Key.ToString());
        //        i++;
        //    }

        //    var result = (from gpt in m_CosmeticDC.ProductInfos
        //                  where pp.Contains(gpt.ProductId)
        //                  select new
        //                  {
        //                      ProductId = gpt.ProductId,
        //                      ProductName = gpt.Name,
        //                      ProductUnitPrice = gpt.网络零售价.ToString().Remove(gpt.网络零售价.ToString().Length - 2),//double.Parse(gpt.网络零售价.ToString()),//
        //                      PictureUrl = gpt.PictureUrl,
        //                      ProductWeight = gpt.weight
        //                      //Count = ht[gpt.ProductId.ToString()].ToString(),
        //                      //ProductSmallPrice ="1"// Convert.ToInt32(ht[gpt.ProductId]) * gpt.Price,
        //                  }).ToList();

        //    return result;
        //}

        ///// <summary>
        ///// 
        ///// </summary>
        ///// <param name="ht"></param>
        ///// <returns></returns>
        //public List<ProductInfo> GetPackageProducts(Hashtable ht)
        //{
        //    Guid[] pp = new Guid[ht.Count];
        //    int i = 0;

        //    foreach (DictionaryEntry entry in ht)
        //    {
        //        pp[i] = new Guid(entry.Key.ToString());
        //        i++;
        //    }
        //    List<ProductInfo> pis = new List<ProductInfo>();

        //    List<PackageSale> result = (from gpt in m_CosmeticDC.PackageSales
        //                                where pp.Contains(gpt.Id)
        //                                select gpt).ToList();
        //    foreach (PackageSale item in result)
        //    {
        //        ProductInfo pi1 = (from pinfo in m_CosmeticDC.ProductInfos
        //                           where pinfo.ProductId == item.ProductId1
        //                           select pinfo).Single();
        //        ProductInfo pi2 = (from pinfo in m_CosmeticDC.ProductInfos
        //                           where pinfo.ProductId == item.ProductId2
        //                           select pinfo).Single();
        //        ProductInfo pi3 = (from pinfo in m_CosmeticDC.ProductInfos
        //                           where pinfo.ProductId == item.ProductId3
        //                           select pinfo).Single();
        //        pis.Add(pi1);
        //        pis.Add(pi2);
        //        pis.Add(pi3);
        //    }

        //    return pis;


        //}

        //public decimal GetDeliveryPriceByWeightAndArea(int weight, string area)
        //{
        //    var result = from post in m_CosmeticDC.Postages
        //                 where post.City == area
        //                 select post;
        //    if (result == null)
        //    {
        //        return 0;
        //    }

        //    Postage aPostage = result.Single();
        //    int first = weight / 500;
        //    if (first <= 1)
        //    {
        //        return aPostage.Under500g.Value;
        //    }
        //    else
        //    {
        //        return aPostage.Increament.Value * first + aPostage.Under500g.Value;
        //    }
        //}

        //public List<Postage> GetPostageAll()
        //{
        //    var result = from post in m_CosmeticDC.Postages
        //                 select post;

        //    return result.ToList();
        //}

        //public void UpdatePostage(string id, string under500g, string Increament)
        //{
        //    Postage po = (from post in m_CosmeticDC.Postages
        //                  where post.Id == int.Parse(id)
        //                  select post).Single();
        //    po.Under500g = decimal.Parse(under500g);
        //    po.Increament = decimal.Parse(Increament);

        //    m_CosmeticDC.SubmitChanges();
        //}

        ///// <summary>
        ///// get tag by product from all the product comments
        ///// </summary>
        ///// <param name="aProduct">the product</param>
        ///// <returns>tag list</returns>
        //[Obsolete("", true)]
        //public List<Tag> GetTagByProduct(Product aProduct)
        //{
        //    var theTagGroup = from theTag in m_CosmeticDC.Tags
        //                      join theCommentTag in m_CosmeticDC.CommentTags on theTag.TagId equals theCommentTag.TagId
        //                      where theCommentTag.Comment.ProductId == aProduct.ProductId
        //                      group theCommentTag by theCommentTag.TagId;

        //    List<Tag> theTagList = new List<Tag>();
        //    foreach (var theTag in theTagGroup)
        //    {
        //        Tag theNewTag = new Tag();
        //        theNewTag.Counts = theTag.Count();
        //        theNewTag.Name = theTag.ToList()[0].Tag.Name;
        //        theNewTag.TagId = theTag.ToList()[0].Tag.TagId;
        //        theTagList.Add(theNewTag);
        //    }
        //    return theTagList.OrderByDescending(theTag => theTag.Counts).ToList();
        //}

        ///// <summary>
        ///// 
        ///// </summary>
        ///// <param name="ProductId"></param>
        ///// <returns></returns>
        //public bool CheckProductStock(Guid ProductId)
        //{
        //    var result = from ginfo in m_CosmeticDC.ProductInfos
        //                 where ginfo.ProductId == ProductId
        //                 select ginfo;
        //    //如果视图 ProductInfo 里没有该商品
        //    if (result.Count() == 0)
        //    {
        //        return false;
        //    }

        //    if (result.Single().在库数量 != 0)
        //    {//如果该产品在库数量不为0，返回true
        //        return true;
        //    }
        //    else
        //    {//否则返回false
        //        return false;
        //    }
        //}

        //public bool CheckProductCategory(string Pid, string CategoryName)
        //{
        //    string CategoryId = (from category in m_CosmeticDC.Categories
        //                         where category.Name == CategoryName
        //                         select category.CategoryId.ToString()).Single();

        //    string ProductCategoryId = (from product in m_CosmeticDC.Products
        //                                where product.ProductId == new Guid(Pid)
        //                                select product.ProductCategoryId.ToString()).Single();

        //    List<string> middleCategoryIds = (from cate in m_CosmeticDC.Categories
        //                                      where cate.SupCategoryId == new Guid(CategoryId)
        //                                      select cate.CategoryId.ToString()).ToList();

        //    if (middleCategoryIds.Contains(ProductCategoryId))
        //    {
        //        return true;
        //    }
        //    else
        //    {
        //        return false;
        //    }
        //}

        //#region Search

        ///// <summary>
        ///// search product
        ///// </summary>
        ///// <param name="aParameters">search keyward dictionary. ie: Name:美白, Size:100ml</param>
        ///// <returns>product list</returns>
        ////public List<Product> ProductSearch(Dictionary<string,string> aParameters)
        ////{
        ////    IQueryable<Product> theProductList	= m_CosmeticDC.Products;

        ////    foreach (string theKey in aParameters.Keys)
        ////    {
        ////        string theValue		= aParameters[theKey];
        ////        if (theKey.Equals("Name"))
        ////        {
        ////            foreach (string theKeyWord in theValue.Split(" ".ToCharArray(), StringSplitOptions.RemoveEmptyEntries))
        ////            {
        ////                theProductList	= (from theProduct in theProductList
        ////                                   where theProduct.Name.Contains(theKeyWord)
        ////                                   select theProduct);
        ////            }
        ////        }
        ////        else if (theKey.Equals("Tag"))
        ////        {
        ////            foreach (string theKeyWord in theValue.Split(" ".ToCharArray(), StringSplitOptions.RemoveEmptyEntries))
        ////            {
        ////                theProductList	= (from theProduct in theProductList
        ////                                   join theProductTag in m_CosmeticDC.ProductTags on theProduct.ProductId equals theProductTag.ProductId
        ////                                   where theProductTag.Tag.Name == theKeyWord
        ////                                   select theProduct);
        ////            }
        ////        }
        ////        else if (theKey.Equals("Comment"))
        ////        {
        ////            foreach (string theKeyWord in theValue.Split(" ".ToCharArray(), StringSplitOptions.RemoveEmptyEntries))
        ////            {
        ////                theProductList	= (from theProduct in theProductList
        ////                                   join theCommand in m_CosmeticDC.Comments on theProduct.ProductId equals theCommand.ProductId
        ////                                   where theCommand.Content.Contains(theKeyWord)
        ////                                   select theProduct);
        ////            }
        ////        }
        ////        else if (theKey.Equals("Brand"))
        ////        {
        ////            theProductList	= (from theProduct in theProductList
        ////                                   where  theProduct.BrandId == new Guid(theValue)
        ////                                   select theProduct);
        ////        }
        ////        else if (theKey.Equals("Series"))
        ////        {
        ////            theProductList	= (from theProduct in theProductList
        ////                                   where  theProduct.ProductSeriesId == new Guid(theValue)
        ////                                   select theProduct);
        ////        }
        ////        else if (theKey.Equals("ProductCategory"))
        ////        {
        ////            theProductList	= (from theProduct in theProductList
        ////                                   where  theProduct.ProductCategoryId == new Guid(theValue)
        ////                                   select theProduct);
        ////        }
        ////        else if (theKey.Equals("Published"))
        ////        {
        ////            theProductList	= (from theProduct in theProductList
        ////                                   where  theProduct.PublishDate.AddMonths(Convert.ToInt32(theValue)) > DateTime.Today
        ////                                   select theProduct);
        ////        }
        ////        else if (theKey.Equals("Age"))
        ////        {
        ////            theProductList	= (from theProduct in theProductList
        ////                                   join theCommand in m_CosmeticDC.Comments on theProduct.ProductId equals theCommand.ProductId
        ////                                   where Convert.ToInt32(theValue)-10 < (DateTime.Today-theCommand.User.Birthday).Days/365
        ////                                   && (DateTime.Today-theCommand.User.Birthday).Days/365 < Convert.ToInt32(theValue)
        ////                                   select theProduct);
        ////        }
        ////        else if (theKey.Equals("Hair"))
        ////        {
        ////            theProductList	= (from theProduct in theProductList
        ////                                   join theCommand in m_CosmeticDC.Comments on theProduct.ProductId equals theCommand.ProductId
        ////                                   where theCommand.User.UserHairCategoryId == new Guid(theValue)
        ////                                   select theProduct);
        ////        }
        ////        else if (theKey.Equals("Skin"))
        ////        {
        ////            theProductList	= (from theProduct in theProductList
        ////                                   join theCommand in m_CosmeticDC.Comments on theProduct.ProductId equals theCommand.ProductId
        ////                                   where theCommand.User.UserSkinCategoryId == new Guid(theValue)
        ////                                   select theProduct);
        ////        }
        ////    }

        ////    return theProductList.Distinct().ToList();
        ////}

        //#endregion





        //#region Product Tag

        ///// <summary>
        ///// get product tag by product tag id
        ///// </summary>
        ///// <param name="aProductTagId">the special product tag id</param>
        ///// <returns>product tag</returns>
        //public ProductTag GetProductTagById(Guid aProductTagId)
        //{
        //    List<ProductTag> theTagList = (from theProductTag in m_CosmeticDC.ProductTags
        //                                   where theProductTag.ProductTagId == aProductTagId
        //                                   select theProductTag).ToList();

        //    if (theTagList.Count == 1)
        //    {
        //        return theTagList[0];
        //    }
        //    return null;
        //}
        ///// <summary>
        ///// get product tag by tag id and product id
        ///// </summary>
        ///// <param name="aTagId">the special tag id</param>
        ///// <param name="aProductId">the special product id</param>
        ///// <returns>product tag</returns>
        //public static ProductTag GetProductTagById(CosmeticORMDataContext m_CosmeticDC, Guid aTagId, Guid aProductId)
        //{
        //    List<ProductTag> theTagList = (from theProductTag in m_CosmeticDC.ProductTags
        //                                   where theProductTag.TagId == aTagId && theProductTag.ProductId == aProductId
        //                                   select theProductTag).ToList();
        //    if (theTagList.Count == 1)
        //    {
        //        return theTagList[0];
        //    }
        //    return null;
        //}

        ///// <summary>
        ///// the special product tag counts+1
        ///// </summary>
        ///// <param name="aProductTag">the special product tag</param>
        //public static void ProductTagAddCount(CosmeticORMDataContext m_CosmeticDC, ProductTag aProductTag)
        //{
        //    aProductTag.Counts++;
        //    aProductTag.LastUseTime = DateTime.Now;
        //    m_CosmeticDC.SubmitChanges();
        //}
        ///// <summary>
        ///// the special product tag counts+1
        ///// </summary>
        ///// <param name="aTagId">the tag id</param>
        ///// <param name="aProductId">product id</param>
        //public static void ProductTagAddCount(CosmeticORMDataContext m_CosmeticDC, Guid aTagId, Guid aProductId)
        //{
        //    ProductTag theProductTag = GetProductTagById(m_CosmeticDC, aTagId, aProductId);
        //    if (null != theProductTag)
        //    {
        //        theProductTag.Counts++;
        //        theProductTag.LastUseTime = DateTime.Now;
        //        m_CosmeticDC.SubmitChanges();
        //    }
        //    else
        //    {
        //        ProductTag theNewProductTag = new ProductTag();
        //        theNewProductTag.Counts = 1;
        //        theNewProductTag.CreateTime = DateTime.Now;
        //        theNewProductTag.LastUseTime = DateTime.Now;
        //        theNewProductTag.ProductId = aProductId;
        //        theNewProductTag.ProductTagId = Guid.NewGuid();
        //        theNewProductTag.TagId = aTagId;
        //        m_CosmeticDC.ProductTags.InsertOnSubmit(theNewProductTag);
        //        m_CosmeticDC.SubmitChanges();
        //    }
        //}


        ///// <summary>
        ///// the special product tag counts-1
        ///// </summary>
        ///// <param name="aProductTag">the special product tag</param>
        //public static void ProductTagMinusCount(CosmeticORMDataContext m_CosmeticDC, ProductTag aProductTag)
        //{
        //    if (null == aProductTag)
        //    {
        //        return;
        //    }

        //    if (aProductTag.Counts == 1)
        //    {
        //        m_CosmeticDC.ProductTags.DeleteOnSubmit(aProductTag);
        //    }
        //    else
        //    {
        //        aProductTag.Counts--;
        //    }
        //    m_CosmeticDC.SubmitChanges();
        //}
        ///// <summary>
        ///// the special product tag counts-1
        ///// </summary>
        ///// <param name="aTagId">the tag id</param>
        ///// <param name="aProductId">product id</param>
        //public static void ProductTagMinusCount(CosmeticORMDataContext m_CosmeticDC, Guid aTagId, Guid aProductId)
        //{
        //    ProductTagMinusCount(m_CosmeticDC, GetProductTagById(m_CosmeticDC, aTagId, aProductId));
        //}
        ///*
        //        public static void ProductTagMinusCount(string aTagName, Guid aProductId)
        //        {
        //            Tag theTag	= this.m_TagManager.GetTagByName(aTagName, TagManager.ETagCategory.Product);
        //            this.ProductTagMinusCount(theTag.TagId, aProductId);
        //        }
        //*/


        ///// <summary>
        ///// get product tag by product
        ///// </summary>
        ///// <param name="aProductId">product id</param>
        ///// <returns>product tag list</returns>
        //public List<ProductTag> GetTagByProduct(Guid aProductId)
        //{
        //    return (from theProductTag in m_CosmeticDC.ProductTags
        //            where theProductTag.ProductId == aProductId
        //            && theProductTag.Counts > 0
        //            orderby theProductTag.Counts descending
        //            select theProductTag).ToList();
        //}

        //#endregion




        //#region Recommend Produt

        ///// <summary>
        ///// search recommand product
        ///// </summary>
        ///// <returns>product list</returns>
        //public List<RecommendProduct> GetAllRecommendProducts()
        //{
        //    return (from theRecommend in m_CosmeticDC.RecommendProducts
        //            select theRecommend).ToList();
        //}

        ///// <summary>
        ///// search recommand product
        ///// </summary>
        ///// <returns>product list</returns>
        //public List<RecommendProduct> GetRecommendProducts()
        //{
        //    return (from theRecommend in m_CosmeticDC.RecommendProducts
        //            where theRecommend.Recommend
        //            select theRecommend).ToList();
        //}




        ///// <summary>
        ///// search recommand product
        ///// </summary>
        ///// <returns>product list</returns>
        //public List<RecommendProduct> GetFocusProducts()
        //{
        //    return (from theRecommend in m_CosmeticDC.RecommendProducts
        //            where theRecommend.Focus
        //            select theRecommend).ToList();
        //}

        ///// <summary>
        ///// search point product
        ///// </summary>
        ///// <returns>product list</returns>
        //public List<RecommendProduct> GetPointProducts()
        //{
        //    return (from theRecommend in m_CosmeticDC.RecommendProducts
        //            where theRecommend.Point
        //            select theRecommend).ToList();
        //}


        ///// <summary>
        ///// insert new recommend product into db
        ///// </summary>
        ///// <param name="aRecommendProduct">new recommend product</param>
        //public void AddRecommendProduct(RecommendProduct aRecommendProduct)
        //{
        //    if (m_CosmeticDC.RecommendProducts.Where(theRecommendProduct => theRecommendProduct.ProductId == aRecommendProduct.ProductId).Count() == 0)
        //    {
        //        m_CosmeticDC.RecommendProducts.InsertOnSubmit(aRecommendProduct);
        //        m_CosmeticDC.SubmitChanges();
        //    }
        //}

        ///// <summary>
        ///// delete a recommend product
        ///// </summary>
        ///// <param name="aRecommendProductId">Recommend Product Id</param>
        //public void DeleteRecommendProduct(Guid aRecommendProductId)
        //{
        //    m_CosmeticDC.RecommendProducts.DeleteOnSubmit(GetRecommendProductById(aRecommendProductId));
        //    m_CosmeticDC.SubmitChanges();

        //}

        ///// <summary>
        ///// get recommend product by id
        ///// </summary>
        ///// <param name="aRecommendProductId"></recommend product id>
        ///// <returns>recommend product</returns>
        //public RecommendProduct GetRecommendProductById(Guid aRecommendProductId)
        //{
        //    return m_CosmeticDC.RecommendProducts.FirstOrDefault(theRecommend => theRecommend.RecommendProductId == aRecommendProductId);
        //}


        ///// <summary>
        ///// get recommend product by id
        ///// </summary>
        ///// <param name="aRecommendProductId"></recommend product id>
        ///// <returns>recommend product</returns>
        //public RecommendProduct GetRecommendProductByProductId(Guid aProductId)
        //{
        //    return m_CosmeticDC.RecommendProducts.FirstOrDefault(theRecommend => theRecommend.ProductId == aProductId);
        //}
        //#endregion



        //public void DeletePlateJapaneseCosmetic(int Id)
        //{
        //    PlateJapaneseCosmetic pjc = (from theJC in m_CosmeticDC.PlateJapaneseCosmetics
        //                                 where theJC.Id == Id
        //                                 select theJC).Single();
        //    m_CosmeticDC.PlateJapaneseCosmetics.DeleteOnSubmit(pjc);
        //    m_CosmeticDC.SubmitChanges();
        //}

        //public void DeletePlateHouseHold(int Id)
        //{
        //    PlateHouseHold phh = (from theJC in m_CosmeticDC.PlateHouseHolds
        //                          where theJC.Id == Id
        //                          select theJC).Single();
        //    m_CosmeticDC.PlateHouseHolds.DeleteOnSubmit(phh);
        //    m_CosmeticDC.SubmitChanges();
        //}

        //public void DeletePlateExcellentCloth(int Id)
        //{
        //    PlateExcellentCloth pec = (from theJC in m_CosmeticDC.PlateExcellentCloths
        //                               where theJC.Id == Id
        //                               select theJC).Single();
        //    m_CosmeticDC.PlateExcellentCloths.DeleteOnSubmit(pec);
        //    m_CosmeticDC.SubmitChanges();
        //}


        ///// <summary>
        ///// 
        ///// </summary>
        ///// <returns></returns>
        //public IEnumerable GetPlateJapaneseCosmetic()
        //{
        //    var result = (from theJC in m_CosmeticDC.PlateJapaneseCosmetics
        //                  join theProductInfo in m_CosmeticDC.ProductInfos
        //                  on theJC.ProductId equals theProductInfo.ProductId
        //                  orderby theJC.Sequence
        //                  select new
        //                  {
        //                      Id = theJC.Id,
        //                      ProductId = theProductInfo.ProductId,
        //                      ProductName = theProductInfo.Name,
        //                      PictureUrl = theProductInfo.PictureUrl,
        //                      ProductView = theProductInfo.Views,
        //                      ProductComment = (from theProduct in m_CosmeticDC.Products where theProduct.ProductId == theProductInfo.ProductId select theProduct.Comments.Count).Single()
        //                  }
        //                 ).Distinct();
        //    return result;
        //}
        ///// <summary>
        ///// 
        ///// </summary>
        ///// <returns></returns>
        //public IEnumerable GetPlateHouseHold()
        //{
        //    var result = (from theJC in m_CosmeticDC.PlateHouseHolds
        //                  join theProductInfo in m_CosmeticDC.ProductInfos
        //                  on theJC.ProductId equals theProductInfo.ProductId
        //                  orderby theJC.Sequence
        //                  select new
        //                  {
        //                      Id = theJC.Id,
        //                      ProductId = theProductInfo.ProductId,
        //                      ProductName = theProductInfo.Name,
        //                      PictureUrl = theProductInfo.PictureUrl,
        //                      ProductView = theProductInfo.Views,
        //                      ProductComment = (from theProduct in m_CosmeticDC.Products where theProduct.ProductId == theProductInfo.ProductId select theProduct.Comments.Count).Single()
        //                  }
        //                 ).Distinct();
        //    return result;
        //}

        ///// <summary>
        ///// 
        ///// </summary>
        ///// <returns></returns>
        //public IEnumerable GetPlateExcellentCloth()
        //{
        //    var result = (from theJC in m_CosmeticDC.PlateExcellentCloths
        //                  join theProductInfo in m_CosmeticDC.ProductInfos
        //                  on theJC.ProductId equals theProductInfo.ProductId
        //                  orderby theJC.Sequence
        //                  select new
        //                  {
        //                      Id = theJC.Id,
        //                      ProductId = theProductInfo.ProductId,
        //                      ProductName = theProductInfo.Name,
        //                      PictureUrl = theProductInfo.PictureUrl,
        //                      ProductView = theProductInfo.Views,
        //                      ProductComment = (from theProduct in m_CosmeticDC.Products where theProduct.ProductId == theProductInfo.ProductId select theProduct.Comments.Count).Single()
        //                  }
        //                 ).Distinct();
        //    return result;
        //}


        //public void AddProductToJapaneseProduct(string productid)
        //{
        //    var result = from theCosmetic in m_CosmeticDC.PlateJapaneseCosmetics
        //                 where theCosmetic.ProductId == new Guid(productid)
        //                 select theCosmetic;
        //    if (result.Count() == 0)
        //    {
        //        PlateJapaneseCosmetic pjc = new PlateJapaneseCosmetic();
        //        pjc.CreateTime = DateTime.Now;
        //        pjc.ProductId = new Guid(productid);
        //        pjc.Sequence = (from thepjc in m_CosmeticDC.PlateJapaneseCosmetics orderby thepjc.Sequence descending select thepjc).Take(1).Single().Sequence + 1;

        //        m_CosmeticDC.PlateJapaneseCosmetics.InsertOnSubmit(pjc);
        //        m_CosmeticDC.SubmitChanges();
        //    }
        //}

        //public void AddProductToHouseHold(string productid)
        //{
        //    var result = from theHouseHold in m_CosmeticDC.PlateHouseHolds
        //                 where theHouseHold.ProductId == new Guid(productid)
        //                 select theHouseHold;
        //    if (result.Count() == 0)
        //    {
        //        PlateHouseHold phh = new PlateHouseHold();

        //        phh.CreateTime = DateTime.Now;
        //        phh.ProductId = new Guid(productid);
        //        phh.Sequence = (from thephh in m_CosmeticDC.PlateHouseHolds orderby thephh.Sequence descending select thephh).Take(1).Single().Sequence + 1;

        //        m_CosmeticDC.PlateHouseHolds.InsertOnSubmit(phh);
        //        m_CosmeticDC.SubmitChanges();
        //    }
        //}

        //public void AddProductToCloth(string productid)
        //{
        //    var result = from theCloth in m_CosmeticDC.PlateExcellentCloths
        //                 where theCloth.ProductId == new Guid(productid)
        //                 select theCloth;
        //    if (result.Count() == 0)
        //    {
        //        //PlateHouseHold phh = new PlateHouseHold();
        //        PlateExcellentCloth pec = new PlateExcellentCloth();

        //        pec.CreateTime = DateTime.Now;
        //        pec.ProductId = new Guid(productid);
        //        pec.Sequence = (from thepec in m_CosmeticDC.PlateExcellentCloths orderby thepec.Sequence descending select thepec).Take(1).Single().Sequence + 1;

        //        m_CosmeticDC.PlateExcellentCloths.InsertOnSubmit(pec);
        //        m_CosmeticDC.SubmitChanges();
        //    }
        //}

        ///// <summary>
        ///// 
        ///// </summary>
        ///// <param name="aName"></param>
        ///// <returns></returns>
        //public List<ProductInfo> SearchProductInfoByPrice(string aName)
        //{
        //    string[] theNames = aName.Split('|'); //第一个数小，第二个数大。例：10，20
        //    IQueryable<ProductInfo> theProductInfos = m_CosmeticDC.ProductInfos;

        //    theProductInfos = SearchProductInfoByPrice(theProductInfos, Convert.ToDecimal(theNames[0]), Convert.ToDecimal(theNames[1]));// SearchProductInfoByName(theProductInfos, theName);

        //    return theProductInfos.ToList();
        //}


        ///// <summary>
        ///// 
        ///// </summary>
        ///// <param name="aProductInfos"></param>
        ///// <param name="aName"></param>
        ///// <returns></returns>
        //public IQueryable<ProductInfo> SearchProductInfoByPrice(IQueryable<ProductInfo> aProductInfos, decimal priceBlow, decimal priceTop)
        //{
        //    return (from theProductInfo in aProductInfos
        //            where theProductInfo.网络零售价 >= priceBlow & theProductInfo.网络零售价 <= priceTop
        //            select theProductInfo);
        //}
        ///// <summary>
        ///// 得到到该用户所有的商品评论。
        ///// </summary>
        ///// <param name="userId"></param>
        ///// <returns></returns>
        //public static List<EntityComment> GetAllCommentByUserId(string userId)
        //{
        //    List<EntityComment> listComment = new List<EntityComment>();
        //    string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
        //    SqlConnection con = new SqlConnection(conStr);
        //    try
        //    {
        //        con.Open();
        //        string cmdstr = "select com.[ProductId],com.[CreateTime], proIn.[Name],proIn.[PictureUrl],com.[Content] from dbo.ProductInfo as proIn inner join dbo.Comment as com on proIn.[ProductId] = com.[ProductId] where com.[UserId]='" + userId + "'";
        //        SqlCommand cmd = new SqlCommand(cmdstr, con);
        //        DataSet ds = new DataSet();
        //        SqlDataAdapter adap = new SqlDataAdapter(cmd);
        //        adap.Fill(ds);
        //        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        //        {
        //            EntityComment thecom = new EntityComment();
        //            thecom.Content = string.IsNullOrEmpty(ds.Tables[0].Rows[i]["Content"].ToString()) ? " " : ds.Tables[0].Rows[i]["Content"].ToString();
        //            thecom.CreateTime = string.IsNullOrEmpty(ds.Tables[0].Rows[i]["CreateTime"].ToString()) ? "2009-1-1" : ds.Tables[0].Rows[i]["CreateTime"].ToString();
        //            thecom.Name = string.IsNullOrEmpty(ds.Tables[0].Rows[i]["Name"].ToString()) ? "  " : ds.Tables[0].Rows[i]["Name"].ToString();
        //            thecom.PictureUrl = string.IsNullOrEmpty(ds.Tables[0].Rows[i]["PictureUrl"].ToString()) ? "  " : ds.Tables[0].Rows[i]["PictureUrl"].ToString();
        //            thecom.ProductId = string.IsNullOrEmpty(ds.Tables[0].Rows[i]["ProductId"].ToString()) ? "  " : ds.Tables[0].Rows[i]["ProductId"].ToString();
        //            listComment.Add(thecom);
        //        }
        //        return listComment;
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }

        //}

        ///// <summary>
        ///// Search products by price. 10.19
        ///// </summary>
        ///// <param name="priceBlow"></param>
        ///// <param name="priceTop"></param>
        ///// <returns></returns>
        //public List<ProductInfo> SearchProductInfoByPrice(decimal priceBlow, decimal priceTop)
        //{
        //    IQueryable<ProductInfo> theProductInfos = m_CosmeticDC.ProductInfos;
        //    return (from theProductInfo in theProductInfos
        //            where theProductInfo.网络零售价 >= priceBlow & theProductInfo.网络零售价 <= priceTop
        //            select theProductInfo).ToList();
        //}

        ///// <summary>
        ///// 将商品插入本月热销表。
        ///// </summary>
        ///// <param name="productId"></param>
        //public void InsertMonthSale(string productId)
        //{
        //    string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
        //    SqlConnection con = new SqlConnection(conStr);
        //    try
        //    {
        //        con.Open();

        //        string cmdStrGetSe = "select top 1(Sequence) from dbo.MonthlySales order by Sequence desc";
        //        SqlCommand cmdSe = new SqlCommand(cmdStrGetSe, con);
        //        object sequence = cmdSe.ExecuteScalar();
        //        int m_sequence = 0;
        //        if (!string.IsNullOrEmpty(sequence.ToString()))
        //        {
        //            m_sequence = int.Parse(sequence.ToString());
        //        }
        //        m_sequence++;
        //        string cmdStr = "INSERT INTO [dbo].[MonthlySales]([ProductId] ,[Sequence])  VALUES ('" + productId + "' ," + m_sequence + ")";
        //        SqlCommand com = new SqlCommand(cmdStr, con);
        //        com.ExecuteNonQuery();
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //}

        ///// <summary>
        ///// 得到所有的本月执销。
        ///// </summary>
        //public DataSet SelectAllMonthSale()
        //{
        //    string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
        //    SqlConnection con = new SqlConnection(conStr);
        //    try
        //    {
        //        con.Open();
        //        string cmdStr = "select ms.Id, pro.[PictureUrl],pro.[Name],ms.[Sequence],ms.[ProductId] from dbo.[MonthlySales] as ms inner join dbo.[ProductInfo] as pro on ms.[ProductId] = pro.[ProductId] order by ms.Sequence";
        //        SqlCommand com = new SqlCommand(cmdStr, con);
        //        SqlDataAdapter adap = new SqlDataAdapter(com);
        //        DataSet ds = new DataSet();
        //        adap.Fill(ds);
        //        return ds;
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //}

        ///// <summary>
        ///// 取消该商品的本月热销。
        ///// </summary>
        ///// <param name="productId"></param>
        //public void DeleteMonthSale(string productId)
        //{
        //    string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
        //    SqlConnection con = new SqlConnection(conStr);
        //    try
        //    {
        //        con.Open();
        //        string cmdStr = "delete from [dbo].[MonthlySales] where ProductId ='" + productId + "'";
        //        SqlCommand com = new SqlCommand(cmdStr, con);
        //        com.ExecuteNonQuery();
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //}

        //public void UpdateMonthSale(string Id, int Sequence)
        //{
        //    string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
        //    SqlConnection con = new SqlConnection(conStr);
        //    try
        //    {
        //        con.Open();
        //        string cmdStr = "update [dbo].[MonthlySales] set Sequence = '" + Sequence + "' where Id ='" + Id + "'";
        //        SqlCommand com = new SqlCommand(cmdStr, con);
        //        com.ExecuteNonQuery();
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //}

        ///// <summary>
        ///// 取消该商品的本周热销。
        ///// </summary>
        ///// <param name="productId"></param>
        //public void DeleteWeeklySale(string productId)
        //{
        //    string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
        //    SqlConnection con = new SqlConnection(conStr);
        //    try
        //    {
        //        con.Open();
        //        string cmdStr = "delete from [dbo].[WeeklySales] where ProductId ='" + productId + "'";
        //        SqlCommand com = new SqlCommand(cmdStr, con);
        //        com.ExecuteNonQuery();
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //}

        //public void UpdateWeeklySale(string Id, int sequence)
        //{
        //    string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
        //    SqlConnection con = new SqlConnection(conStr);
        //    try
        //    {
        //        con.Open();
        //        string cmdStr = "update [dbo].[WeeklySales] set Sequence = '" + sequence + "'where Id ='" + Id + "'";
        //        SqlCommand com = new SqlCommand(cmdStr, con);
        //        com.ExecuteNonQuery();
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //}


        ///// <summary>
        ///// 得到所有的本周执销。
        ///// </summary>
        //public DataSet SelectAllWeeklySale()
        //{
        //    string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
        //    SqlConnection con = new SqlConnection(conStr);
        //    try
        //    {
        //        con.Open();
        //        string cmdStr = "select ms.Id, pro.[PictureUrl],pro.[Name],ms.[Sequence],ms.[ProductId] from dbo.[WeeklySales] as ms inner join dbo.[ProductInfo] as pro on ms.[ProductId] = pro.[ProductId] order by ms.Sequence";
        //        SqlCommand com = new SqlCommand(cmdStr, con);
        //        SqlDataAdapter adap = new SqlDataAdapter(com);
        //        DataSet ds = new DataSet();
        //        adap.Fill(ds);
        //        return ds;
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //}

        ///// <summary>
        ///// 将商品插入本周热销表。
        ///// </summary>
        ///// <param name="productId"></param>
        //public void InsertWeeklySale(string productId)
        //{
        //    string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
        //    SqlConnection con = new SqlConnection(conStr);
        //    try
        //    {
        //        con.Open();

        //        string cmdStrGetSe = "select top 1(Sequence) from dbo.WeeklySales order by Sequence desc";
        //        SqlCommand cmdSe = new SqlCommand(cmdStrGetSe, con);
        //        object sequence = cmdSe.ExecuteScalar();
        //        int m_sequence = 0;
        //        if (!string.IsNullOrEmpty(sequence.ToString()))
        //        {
        //            m_sequence = int.Parse(sequence.ToString());
        //        }
        //        m_sequence++;
        //        string cmdStr = "INSERT INTO [dbo].[WeeklySales]([ProductId] ,[Sequence])  VALUES ('" + productId + "' ," + m_sequence + ")";
        //        SqlCommand com = new SqlCommand(cmdStr, con);
        //        com.ExecuteNonQuery();
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //}

        ///// <summary>
        ///// 将商品插入组合搭配表。
        ///// </summary>
        ///// <param name="productId"></param>
        //public void InsertPackageSales(string productId1, string productId2, string productId3, decimal allprice)
        //{
        //    string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
        //    SqlConnection con = new SqlConnection(conStr);
        //    try
        //    {
        //        con.Open();

        //        string cmdStr = "INSERT INTO [dbo].[PackageSales]([Id] ,[ProductId1] ,[ProductId2],[ProductId3] ,[CreateTime] ,[CurrentPrice],[ViewTimes]) VALUES(newid() ,'" + productId1 + "','" + productId2 + "','" + productId3 + "',getdate() ," + allprice + " ,0)";
        //        SqlCommand com = new SqlCommand(cmdStr, con);
        //        com.ExecuteNonQuery();
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //}

        ///// <summary>
        ///// select productInfo by product id。
        ///// </summary>
        ///// <param name="productId"></param>
        //public DataSet SelectProductInforByProductId(string productId)
        //{
        //    string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
        //    SqlConnection con = new SqlConnection(conStr);
        //    try
        //    {
        //        con.Open();

        //        string cmdStr = "select ProductId,[Name],PictureUrl,[网络零售价] as price from ProductInfo where ProductId='" + productId + "'";
        //        SqlCommand com = new SqlCommand(cmdStr, con);
        //        SqlDataAdapter adap = new SqlDataAdapter(com);
        //        DataSet ds = new DataSet();
        //        adap.Fill(ds);
        //        return ds;
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //}
        ///// <summary>
        ///// 删除指定的搭配的商品。
        ///// </summary>
        ///// <param name="productId"></param>
        //public void DeleteThePackageSales(string id)
        //{
        //    string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
        //    SqlConnection con = new SqlConnection(conStr);
        //    try
        //    {
        //        con.Open();
        //        string cmdStr = "delete PackageSales where Id='" + id + "'";
        //        SqlCommand com = new SqlCommand(cmdStr, con);
        //        com.ExecuteNonQuery();
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //}


        ///// <summary>
        ///// 获得所有搭配的商品。
        ///// </summary>
        ///// <param name="productId"></param>
        //public DataTable SelectAllPackageSales()
        //{
        //    string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
        //    SqlConnection con = new SqlConnection(conStr);
        //    try
        //    {
        //        con.Open();
        //        string cmdStr = "select Id, (select [Name] from ProductInfo where ProductId= ProductId1) as Name1,(select [Name] from ProductInfo where ProductId= ProductId2) as Name2,(select [Name] from ProductInfo where ProductId= ProductId3) as Name3,CurrentPrice,ViewTimes from PackageSales ";
        //        SqlCommand com = new SqlCommand(cmdStr, con);
        //        SqlDataAdapter adap = new SqlDataAdapter(com);
        //        DataSet ds = new DataSet();
        //        adap.Fill(ds);
        //        return ds.Tables[0];
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //}

        //public static DataTable SearchProductWithMultileContidion(string Keyword, string CateCon, string MinPrice, string MaxPrice)
        //{
        //    string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
        //    SqlConnection con = new SqlConnection(conStr);
        //    try
        //    {
        //        con.Open();
        //        //string cmdStr = "select Id, (select [Name] from ProductInfo where ProductId= ProductId1) as Name1,(select [Name] from ProductInfo where ProductId= ProductId2) as Name2,(select [Name] from ProductInfo where ProductId= ProductId3) as Name3,CurrentPrice,ViewTimes from PackageSales ";
        //        string cmdStr = "select * from ProductInfo where 1=1 ";

        //        if (null != Keyword && Keyword != "")
        //        {
        //            cmdStr += " and [Name] like '%" + Keyword + "%' ";
        //        }

        //        if (null != CateCon && CateCon != "")
        //        {
        //            cmdStr += " and ProductCategoryId in (" + CateCon + ") ";
        //        }

        //        if (null != MinPrice && MinPrice != "")
        //        {
        //            cmdStr += " and 网络零售价 >= " + MinPrice + " ";
        //        }
        //        if (null != MaxPrice && MaxPrice != "")
        //        {
        //            cmdStr += " and 网络零售价 <= " + MaxPrice + " ";
        //        }
        //        SqlCommand com = new SqlCommand(cmdStr, con);
        //        SqlDataAdapter adap = new SqlDataAdapter(com);
        //        DataSet ds = new DataSet();
        //        adap.Fill(ds);
        //        return ds.Tables[0];
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //}

        ///// <summary>
        ///// 获得最高级的某个分类的商品，是本类热销的商品的ProductId
        ///// </summary>
        ///// <param name="aCategoryId"></param>
        ///// <returns></returns>
        //public List<ProductInfo> GetProductInfoByProductId(Guid aProductId)
        //{
        //    var result = (from theProduct in m_CosmeticDC.ProductInfos

        //                  where theProduct.ProductId == aProductId

        //                  select theProduct).ToList();
        //    return result;
        //}

        //public IList GetGoodChoiceProducts()
        //{
        //    var result = (from theProduct in m_CosmeticDC.ProductInfos
        //                  join theGoodChoece in m_CosmeticDC.GoodChoices
        //                  on theProduct.ProductId equals theGoodChoece.ProductId
        //                  orderby theGoodChoece.Sequcnce ascending
        //                  select new
        //                  {
        //                      ProductId = theProduct.ProductId,
        //                      Name = theProduct.Name,
        //                      PictureUrl = theProduct.PictureUrl,
        //                      Price = theProduct.网络零售价,
        //                      Views = theProduct.Views,
        //                      Sequence = theGoodChoece.Sequcnce,
        //                      Id = theGoodChoece.Id
        //                  }
        //                  ).ToList();
        //    return result;
        //}

        //public void UpdateGoodChoiceProducts(string Id, int Sequence)
        //{
        //    var result = (from theGoodChoece in m_CosmeticDC.GoodChoices
        //                  where theGoodChoece.Id == new Guid(Id)
        //                  select theGoodChoece).Single();
        //    result.Sequcnce = Sequence;
        //    m_CosmeticDC.SubmitChanges();
        //}


        //public void InsertGoodChoice(Guid ProductId)
        //{
        //    GoodChoice gc = new GoodChoice();
        //    gc.CreateTime = DateTime.Now;
        //    gc.Id = Guid.NewGuid();
        //    gc.ProductId = ProductId;
        //    gc.Sequcnce = (from theGC in m_CosmeticDC.GoodChoices orderby theGC.Sequcnce descending select theGC.Sequcnce).First().Value + 1;


        //    m_CosmeticDC.GoodChoices.InsertOnSubmit(gc);
        //    m_CosmeticDC.SubmitChanges();
        //}

        //public void DeleteGoodChoiceProduct(Guid ProductId)
        //{
        //    var result = (from thegc in m_CosmeticDC.GoodChoices
        //                  where thegc.ProductId == ProductId
        //                  select thegc).Single();

        //    m_CosmeticDC.GoodChoices.DeleteOnSubmit(result);
        //    m_CosmeticDC.SubmitChanges();
        //}

    }
}
